How to Identify Resource Intensive SQL ("TOP SQL")

2023-10-29 17:28| 来源: 网络整理| 查看: 265

How to Identify Resource Intensive SQL ("TOP SQL") (Doc ID 232443.1)

Last updated on JULY 20, 2023

Applies to: Oracle Database Exadata Express Cloud Service - Version N/A and laterOracle Cloud Infrastructure - Database Service - Version N/A and laterOracle Database Cloud Exadata Service - Version N/A and laterOracle Database Backup Service - Version N/A and laterOracle Database Cloud Service - Version N/A and laterInformation in this document applies to any platform. Goal

This article provides guidance on how to identify the most resource intensive SQL statements in a database for tuning purposes.

If there is a performance issue related to the performance of SQL, it may be that one or two statements are consuming the majority of the resources or it could be that a lot of SQL statements consume small amounts of resources adding up to a large amount. In terms of tuning it is easier to handle the first case with less statements to examine. There are a number of places that you can look to find the highest resource users and how you approach this depends on the area where you are seeing the problem. If the resource issue is high CPU usage then focus on the highest CPU users. If it is I/O then focus on the highest under that criteria. The techniques described here can be used when initial diagnosis of a database performance problem suggests that further investigation needs to concentrate on finding and tuning the most resource intensive SQL statements according to specific criteria, e.g.

Using the most CPU Performing the most disk I/O operations Having the most executions Taking the longest time to execute (elapsed time)

As with any performance issue, once you have identified and tuned a statement, re-test the criteria looking for improvements. If your goal for this statement/overall is met then stop! If your goal is not met then re-tune. If your goal for the statement is met but the overall performance is still not as desired, look for the highest resource user and tune that.

The article should be of use to Database Administrators, Support Engineers, Consultants and Database Performance Analysts.

Solution To view full details, sign in with your My Oracle Support account. Don't have a My Oracle Support account? Click to get started!

In this Document Goal Solution When to Look For Resource Intensive SQL Statements Response Time Analysis shows that heaviest time consumer is CPU-related (e.g. CPU Other or CPU Parse time) or an I/O-related Wait Event (e.g. db file sequential read or db file scattered read): Service Time When "CPU Other" is a significant component of total Response Time: When "CPU Parse" is a significant component of total Response Time: Wait Time When I/O Wait Events are found to be significant components of Response Time: Operating System resource analysis shows that excessive CPU or Disk I/O usage is caused by one or more Oracle processes: Investigation of the performance of a batch job or other session which performs a number of SQL statements and has slow performance: Examination of statistics stored for SQL statements in V$ dynamic views: Top SQL Statements in AWR Reports SQL ordered by Gets SQL ordered by Reads SQL ordered by Executions SQL ordered by Parse Calls (Oracle9i and above) SQL ordered by Sharable Memory SQL ordered by Version Count Top SQL Statements in Statspack Reports Finding SQL with High CPU Other Time in Statspack Finding SQL Statements With High CPU Parse Time in Statspack Finding SQL Statements With High Disk I/O Waits in Statspack Evaluating SQL Statements With Most Executions in Statspack Finding SQL Statements With High Shared Pool Consumption in Statspack Top SQL Statements in V$SQLAREA and V$SQL (without using AWR or STATSPACK) Top SQL Statements in TKPROF Reports References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

【本文地址】

公司简介

联系我们